﻿using System;
using System.Data;
using System.Linq;
using Npgsql;

namespace LayerData.DataBaseManager
{
    public class AccesoBD
    {
        #region Variables

        readonly NpgsqlConnection cn = new NpgsqlConnection();
        readonly string cadenaconexion = "server=localhost;uid=postgres;password=amipro_java*01;database=bd_infosystem;POOLING=True;MINPOOLSIZE=10;MAXPOOLSIZE=20";
        private Boolean transaccion;
        private NpgsqlTransaction tsql;

        #endregion

        #region Metodos

        public void AbrirConexion()
        {
            try
            {
                this.transaccion = false;

                if (this.cn.State != ConnectionState.Open)
                {
                    this.cn.ConnectionString = this.cadenaconexion;
                    this.cn.Open();
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

        public void CerrarConexion()
        {
            try
            {
                this.transaccion = false;
                if (this.cn.State == System.Data.ConnectionState.Open)
                {
                    this.cn.Close();
                    this.cn.Dispose();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void AbrirConexionTrans()
        {
            try
            {
                if (this.transaccion != true)
                {
                    this.AbrirConexion();
                    this.tsql = this.cn.BeginTransaction();
                    this.transaccion = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void CerrarConexionTrans()
        {
            try
            {
                if (this.transaccion == true)
                {
                    this.tsql.Commit();
                    this.CerrarConexion();
                    this.transaccion = false;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void CancelarConexionTrans()
        {
            try
            {
                if (this.transaccion == true)
                {
                    this.tsql.Rollback();
                    this.CerrarConexion();
                    this.transaccion = false;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void ConectarEjecutar(string sql)
        {
            try
            {
                this.AbrirConexion();
                this.Ejecutar(sql);
            }
            catch (Exception)
            {
            }
            finally
            {
                this.CerrarConexion();
            }
        }

        public void Ejecutar(string sql)
        {
            try
            {
                NpgsqlCommand cmd = new NpgsqlCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sql;
                cmd.Connection = this.cn;
                if (this.transaccion == true)
                {
                    cmd.Transaction = this.tsql;
                }
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region Funciones

        public DataTable ConectarConsultar(string sql)
        {
            try
            {
                this.AbrirConexion();
                return this.Consultar(sql);
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
                throw ex;
            }
            finally
            {
                this.CerrarConexion();
            }
        }

        public DataTable Consultar(string sql)
        {
            try
            {
                NpgsqlCommand cmd = new NpgsqlCommand();
                NpgsqlDataAdapter adap = new NpgsqlDataAdapter();
                DataSet dts = new DataSet();
                DataTable dtt = new DataTable();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sql;
                cmd.Connection = this.cn;

                if (this.transaccion == true)
                {
                    cmd.Transaction = this.tsql;
                }
                adap.SelectCommand = cmd;
                adap.Fill(dts, "Consulta");
                dtt = dts.Tables["Consulta"];

                return dtt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public object GenerarCodigo(string tabla, string campo)
        {
            try
            {
                string sql;
                DataTable dtt;
                sql = string.Format("Select isnull(max({0}),0) + 1 from {1}", campo, tabla);
                dtt = this.ConectarConsultar(sql);
                return dtt.Rows[0][0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable ConectarConsultarSP(string sp, params object[] x)
        {
            try
            {
                this.AbrirConexion();
                return this.ConsultarSP(sp, x);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                this.CerrarConexion();
            }
        }

        public DataTable ConsultarSP(string procedimiento, params object[] x)
        {
            try
            {
                NpgsqlCommand cmd = new NpgsqlCommand();
                NpgsqlDataAdapter adap = new NpgsqlDataAdapter();
                DataSet dts = new DataSet();
                DataTable dtt = new DataTable();

                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = procedimiento.Trim();
                cmd.CommandTimeout = 0;
                cmd.Connection = this.cn;

                if (this.transaccion == true)
                {
                    cmd.Transaction = this.tsql;
                }
                //SqlParameter y ;//= default(SqlParameter);
                NpgsqlCommandBuilder.DeriveParameters(cmd);
                //Recupera información de los parámetros del procedimiento almacenado especificado en SqlCommand y rellena la colección de Parameters del objeto SqlCommand especificado.
                int i = 0;
                foreach (NpgsqlParameter y in cmd.Parameters)
                {
                    //if (y.ParameterName != "@RETURN_VALUE")
                    //{
                    y.Value = x[i];
                    i = i + 1;
                    //}
                }
                adap.SelectCommand = cmd;
                adap.Fill(dts, "Consulta");
                dtt = dts.Tables["Consulta"];

                return dtt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int ConectarEjecutarSP(string sp, bool devuelveValor, params object[] x)
        {
            try
            {
                this.AbrirConexion();
                return this.EjecutarSP(sp, devuelveValor, x);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                this.CerrarConexion();
            }
        }

        public int EjecutarSP(string procedimiento, bool devuelveValor, params object[] x)
        {
            try
            {
                NpgsqlCommand cmd = new NpgsqlCommand();

                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = procedimiento.Trim();
                cmd.Connection = this.cn;

                if (this.transaccion == true)
                {
                    cmd.Transaction = this.tsql;
                }

                //	SqlParameter y = default(SqlParameter);
                NpgsqlCommandBuilder.DeriveParameters(cmd);

                int i = 0;
                foreach (NpgsqlParameter y in cmd.Parameters)
                {
                    // if (y.ParameterName != "@RETURN_VALUE")
                    // {
                    y.Value = x[i];
                    i = i + 1;
                    // }
                }

                if (devuelveValor == true)
                {
                    //Return cmd.Parameters(cmd.Parameters.Count - 1).Value
                    NpgsqlDataAdapter adap = new NpgsqlDataAdapter();
                    DataSet dts = new DataSet();
                    DataTable dtt = new DataTable();
                    adap.SelectCommand = cmd;
                    adap.Fill(dts, "Consulta");
                    dtt = dts.Tables["Consulta"];
                    return int.Parse(dtt.Rows[0][0].ToString());
                }
                else
                {
                    cmd.ExecuteNonQuery();
                    return -1;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable EjecutaModelView(string procedure, object[] parametros, NpgsqlConnection con)
        {
            DataTable dtb = new DataTable();
            NpgsqlDataAdapter da = null;
            try
            {
                String query = string.Format("select * from {0}  {1}", procedure, parametros[0]);
                da = new NpgsqlDataAdapter(query, con);
                dtb = new DataTable();
                da.Fill(dtb);
                Console.Write(string.Format("{0}\n", query));
            }
            catch (NpgsqlException ex)
            {
                Console.Write(string.Format("{0}/{1}", ex.Message, ex.Source));
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return dtb;
        }

        #endregion
    }
}